import xlrd
import datetime
import json
import pymysql
import uuid
import decimal
import configparser
from pymysql import IntegrityError, OperationalError


class Test:
    def __init__(self, needUpdate):
        self.needUpdate = needUpdate
        config = configparser.ConfigParser()
        config.read("config.ini")
        host = config.get("dbconfig", "host")
        user = config.get("dbconfig", "user")
        password = config.get("dbconfig", "password")
        db = config.get("dbconfig", "db")
        port = int(config.get("dbconfig", "port"))
        self.createId = int(config.get("dbconfig", "createId"))
        self.updateId = int(config.get("dbconfig", "createId"))
        self.connect = pymysql.connect(host=host, user=user, password=password,
                                       db=db,
                                       port=port)
        self.cursor = self.connect.cursor()
        print("数据库连接成功")

    #     file2  爱企查文件
    def readExcel(self, file2):
        print("正在读取并处理数据...")
        wb1 = xlrd.open_workbook(filename=file2)
        sheet1 = wb1.sheet_by_index(0)
        # dataList2为药监局数据    dataList1为爱企查数据
        dataList1 = []
        i = 3
        while i < sheet1.nrows:
            data = {}
            enterpriseName = str(sheet1.row(i)[0].value)
            if not enterpriseName.endswith("公司") or enterpriseName.endswith("分公司"):
                i += 1
                continue
            data["c_enterprise_name"] = enterpriseName
            data["c_legal_person"] = sheet1.row(i)[1].value
            data["f_register_capital"] = sheet1.row(i)[2].value
            data["d_create_date"] = sheet1.row(i)[3].value
            data["c_province"] = sheet1.row(i)[5].value
            data["c_city"] = sheet1.row(i)[6].value
            data["c_area"] = sheet1.row(i)[7].value
            data["c_phone"] = sheet1.row(i)[13].value
            data["c_address"] = sheet1.row(i)[15].value
            data["c_site"] = sheet1.row(i)[16].value
            data["c_manage_range"] = sheet1.row(i)[18].value
            dataList1.append(data)
            i += 1
        self.dealData(dataList1)

    # 生成id
    def generateId(self):
        return str(uuid.uuid1()).replace("-", "")

    def dealData(self, dataList1):
        for data in dataList1:
            data["c_id"] = self.generateId()
            registerCapital = str(data["f_register_capital"]).strip()
            if "万(元)" in registerCapital:
                registerCapital = registerCapital.replace("万(元)", "")
                if "," in registerCapital:
                    registerCapital = registerCapital.replace(",", "")
                data["f_register_capital"] = decimal.Decimal(registerCapital)
            else:
                data["f_register_capital"] = decimal.Decimal("0.01")
            createDate = str(data["d_create_date"]).strip()[0:10]
            if createDate != "-":
                if "-" in createDate:
                    data["d_create_date"] = datetime.datetime.strptime(createDate, "%Y-%m-%d")
                elif "/" in createDate:
                    data["d_create_date"] = datetime.datetime.strptime(createDate, "%Y/%m/%d")
            else:
                data["d_create_date"] = datetime.datetime.strptime("2222-02-02", "%Y-%m-%d")
            province = data["c_province"]
            if len(data["c_city"]) > 2:
                city = str(data["c_city"]).strip()[0:len(str(data["c_city"])) - 1]
            else:
                city = data["c_city"]
            if len(data["c_area"]) > 2:
                area = str(data["c_area"]).strip()[0:len(str(data["c_area"])) - 1]
            else:
                area = data["c_area"]
            address = data["c_address"]
            #     如果读取的area是-，就从地址里面进行匹配 先匹配县，在匹配第二次出现的市，在匹配第一次出现的市，最后在匹配区
            if area == "-" or area == "":
                m = self.get_area_index(address, "市", 2)
                n = self.get_area_index(address, "区", 2)
                if "县" in address:
                    end_index = address.index("县")
                    start_index = end_index - 2
                    area = address[start_index:end_index]
                    if area in city:
                        area = "-"
                elif m:
                    start_index = m - 2
                    area = address[start_index:m]
                    if area in city:
                        area = "-"
                elif "市" in address:
                    end_index = address.index("市")
                    start_index = end_index - 2
                    area = address[start_index:end_index]
                    if area in city:
                        area = "-"
                if area == "-" and "区" in address:
                    end_index = address.index("区")
                    start_index = end_index - 2
                    area = address[start_index:end_index]
                    if area in city:
                        area = "-"
            data["c_province"] = "-"
            data["c_city"] = "-"
            data["c_area"] = ""
            # 匹配省，市，区(县)
            with open("old_province.json", "r", encoding="UTF-8") as json_object:
                list = json.load(json_object)
                flag = False
                for i in list:
                    if province == i["label"]:
                        data["c_province"] = i["value"]
                        for j in i["children"]:
                            if city in j["label"]:
                                data["c_city"] = j["value"]
                                flag = True
                                if "children" in j.keys():
                                    for k in j["children"]:
                                        if area in k["label"]:
                                            data["c_area"] = k["value"]
                                            break
                            if flag:
                                break
                    if flag:
                        break
            manageRange = str(data["c_manage_range"]).replace("一般", "")
            rangeList = manageRange.split(";")
            s = ""
            for range in rangeList:
                # 第Ⅰ、Ⅱ、Ⅲ类医疗器械
                if "一" in range and ("售" in range or "经营" in range or "批发" in range or "销" in range) or (
                        "Ⅰ" in range and
                        ("售" in range or "经营" in range or "批发" in range or "销" in range)) and "1" not in s:
                    s += ",1"

                if "二" in range and ("售" in range or "经营" in range or "批发" in range or "销" in range) or (
                        "Ⅱ" in range and
                        ("售" in range or "经营" in range or "批发" in range or "销" in range)) and "2" not in s:
                    s += ",2"
                if "三" in range and ("售" in range or "经营" in range or "批发" in range or "销" in range) or (
                        "Ⅲ" in range and (
                        "售" in range or "经营" in range or "批发" in range or "销" in range)) and "3" not in s:
                    s += ",3"
                if ( "药" in range and "售" in range or "药" in range and "经营" in range or "药" in range and "批发" in range or "药" in range and "销" in range) and "4" not in s:
                    s += ",4"
            if s.startswith(","):
                s = s[1:]
            if s == "":
                if "一类" in manageRange or "二类" in manageRange or "三类" in manageRange or "Ⅰ类" in manageRange or "Ⅱ类" in manageRange or "Ⅲ类" in manageRange:
                    s = "5"
                else:
                    if "医疗器械" in manageRange:
                        s = "1"
                    else:
                        s = "5"
            data["c_sale_type"] = s
            # 设置 创建人 创建时间
            time = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
            data["dt_create_time"] = time
            data["dt_update_time"] = time
        for i in dataList1:
            print("处理过后的数据:>>>>>>>", i)

        self.importData(dataList1)

    def importData(self, dataList1):
        dd = ()

        # connect = pymysql.connect(host='124.71.29.162', user='root', password='St1282001316*',
        #                           db='scrapy_hospital',
        #                           port=3306)
        # cursor = connect.cursor()
        # print("数据库连接成功")

        insertsql = 'INSERT INTO t_nmd_mp_enterprise_sale (c_id,c_enterprise_name,c_province,c_city,c_area,f_register_capital,d_create_date,' \
                    'c_sale_type,c_legal_person,c_phone, c_site,c_address,c_manage_range,n_create_id, dt_create_time,n_update_id, dt_update_time) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'

        updatesql = 'update t_nmd_mp_enterprise_sale set c_province=%s,c_city=%s,c_area=%s,f_register_capital = %s,d_create_date=%s,' \
                    'c_sale_type=%s,c_legal_person=%s,c_phone=%s,c_site=%s,c_address=%s,c_manage_range=%s,n_update_id=%s, dt_update_time=%s where c_enterprise_name=%s'

        successnum = 0
        filenum = 0
        updatenum = 0
        failList = []
        for data in dataList1:
            try:
                self.cursor.execute(insertsql,
                                    (data["c_id"], data["c_enterprise_name"], data['c_province'], data['c_city'],
                                     data['c_area'],
                                     data['f_register_capital'], data['d_create_date'], data['c_sale_type'],
                                     data['c_legal_person'], data['c_phone'], data['c_site'], data['c_address'],
                                     data['c_manage_range'], self.createId, data["dt_create_time"], self.updateId,
                                     data["dt_update_time"]))
                print(data["c_enterprise_name"], "插入成功")
                successnum += 1
                self.connect.commit()
            except IntegrityError:
                try:
                    if self.needUpdate:
                        self.cursor.execute(updatesql,
                                            (data['c_province'], data['c_city'],
                                             data['c_area'], data['f_register_capital'],
                                             data['d_create_date'], data['c_sale_type'],
                                             data['c_legal_person'], data['c_phone'], data['c_site'],
                                             data['c_address'],
                                             data['c_manage_range'], self.updateId,
                                             data["dt_update_time"], data["c_enterprise_name"]))
                        self.connect.commit()
                        print(data["c_enterprise_name"], "更新成功")
                        updatenum += 1
                except IntegrityError:
                    print(data["c_enterprise_name"], "插入失败")
                    filenum += 1
                    fail = {"c_enterprise_name": data["c_enterprise_name"]}
                    failList.append(fail)
                except OperationalError:
                    print(data["c_enterprise_name"], "插入失败")
                    filenum += 1
                    fail = {"c_enterprise_name": data["c_enterprise_name"]}
                    failList.append(fail)

        self.close_db(successnum, updatenum, filenum, failList)

    def close_db(self, successnum, updatenum, failnum, failList):
        self.cursor.close()
        self.connect.close()
        s = "导入完成,插入{}条,更新{}条,失败{}条".format(successnum, updatenum, failnum)
        now = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S").replace("-", "").replace(":", "").replace(" ", "")
        with open('{}.txt'.format(now), 'w') as f:
            f.write(s + '\r')
            if failnum > 0:
                f.write('\t【插入失败的数据】\r')
                for l in failList:
                    for k, v in l.items():
                        f.write(str(v))
                        f.write('\r')
                f.write('\r\r')
        print(s)

        # 如果address中 "市" 出现两次，则获取"市"第二次出现的下标

    def get_area_index(self, address, city, num):
        count = address.count(city)
        if count > 1:
            i = 0
            index = -1
            while i < num:
                index = address.find(city, index + 1)
                i += 1
            return index
        else:
            return None


# host,user,password,db,port
if __name__ == '__main__':

    file2 = input("输入爱企查的文件名")
    # host = input("输入数据库地址")
    # user = input("输入数据库user")
    # password = input("输入数据库密码")
    # db = input("输入数据库名")
    # port = int(input("输入数据库端口号"))
    # file = "buchong.xls"
    # test = Test(host, user, password, db, port)
    inputstr = input("是否要更新，1-更新，2-不更新")
    start_time = datetime.datetime.now()
    # print("开始时间:>>", start_time)
    needUpdate = False
    if inputstr == "1":
        needUpdate = True
    test = Test(needUpdate)
    test.readExcel(file2)
    end_time = datetime.datetime.now()
    # print("结束时间:>>>", end_time)
    use_time = (end_time - start_time).seconds
    use_min = int(use_time / 60)
    use_sec = use_time % 60
    if use_min > 1:
        print("用时:{}分{}秒".format(use_min, use_sec))
    else:
        print("用时:{}秒".format(use_sec))
